<?php

namespace Home\Controller;

use Home\Org\Util\Sqlserver;
use Think\Model;

use Home\Org\Util\UserMappingItemcategory;

/**
 * 商品
 * @author dem
 *
 */
class GoodsController extends HomeController
{

    function relation()
    {
        session('u_code', null);
        $this->assign('src', '/sap/goods.php');
        $this->display('report/index');
    }

    function index()
    {
        $this->assign('src', '');
        $this->display('report/index');
    }

    function search()
    {
        $this->assign('src', '/sap/goods_search.php');
        $this->display('report/index');
    }

    function search2()
    {
        $this->assign('src', '/sap/goods_search2.php');
        $this->display('report/index');
    }

    function queryItem()
    {
        $key = I('key');
        $where = '';
        if ($key) {
            $where .= " where name like '%{$key}%'";
        }

        $model = $this->M2('product');
        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize');
        $countsql = "SELECT count(1) as total from  mallbuilder_product as a LEFT JOIN mallbuilder_product_setmeal as b on a.id=b.pid {$where}";
        $limit = ' limit ' . ($pageIndex * $pageSize) . ',' . $pageSize;
        $quersql = "SELECT `code`,sku,`name`,setmeal ,a.price from  mallbuilder_product as a LEFT JOIN mallbuilder_product_setmeal as b on a.id=b.pid {$where} order by a.id desc {$limit} ";
        $total = $model->query($countsql)[0]['total'];
        $res = $model->query($quersql);
        $newData = array();
        foreach ($res as $key => $item) {
            if (!$item['sku'])
                $newData[$key]['catid'] = $item['code'];
            else
                $newData[$key]['catid'] = $item['sku'];
            $newData[$key]['name'] = $item['name'] . ' ' . $item['setmeal'];
            $newData[$key]['U_TypeOption'] = $item['setmeal'];
            $newData[$key]['price'] = $item['price'];
        }
        $data = array('total' => $total, 'data' => $newData);

        echo json_encode($data);
        exit;
    }

    /**
     * sap数据库表操作
     * @param unknown $tableName
     */
    function M2($tableName)
    {

        return M($tableName, 'think_', 'DB_CONFIG2');
    }

    function queryItemBySap()
    {
        import("@.ORG.Util.Sqlserver");
        $sqlObj = new Sqlserver();
        $key = I('key');
        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize');
        $nm = $pageIndex * $pageSize;
        $where = '';
        if ($key) {
            $key = iconv('utf-8', 'GBK', $key);
            $where = " where t3.itemcode+t3.itemname like '%{$key}%'";
        }
        $totalSql = "select count(1) as total from OITM t3 left join (select t0.ItemCode,isnull(unitprice,'0') unitprice from (select itemcode from oitw group by itemcode) t0 left join (select itemcode,price/qty unitprice from (select itemcode,sum(onhand*avgprice) price,sum(onhand) qty, abc = case when sum(onhand) = 0 then '0' when sum(onhand) <> 0 then '1' end from oitw where whscode in ('A01','A02','A03','A04','A11') group by itemcode) t1 where abc <> 0) t2 on t0.itemcode = t2.itemcode) t4 on t3.itemcode = t4.itemcode  {$where}";

        $total = $sqlObj->query($totalSql)[0]['total'];
        $sql = "SELECT top {$pageSize} * from ( select row_number() over(order by t3.AtcEntry) as rownumber ,t3.itemcode catid ,itemname name,u_color U_TypeOption ,unitprice price from OITM t3 left join (select t0.ItemCode,isnull(unitprice,'0') unitprice from (select itemcode from oitw group by itemcode) t0 left join (select itemcode,price/qty unitprice from (select itemcode,sum(onhand*avgprice) price,sum(onhand) qty, abc = case when sum(onhand) = 0 then '0' when sum(onhand) <> 0 then '1' end from oitw where whscode in ('A01','A02','A03','A04','A11') group by itemcode) t1 where abc <> 0) t2 on t0.itemcode = t2.itemcode) t4 on t3.itemcode = t4.itemcode  {$where} ) A WHERE rownumber >{$nm}";
        $res = $sqlObj->query($sql);
        $data = array('total' => $total, 'data' => $res);
        echo json_encode($data);
        exit;
    }

    /**
     * 简洁数据  只查询物料Code name  无分页
     */
    function queryItemBySapSimple()
    {
        // 2017-11-07 过滤物料 ->
        $emp_no = $_SESSION['emp_no'];
        import("@.ORG.Util.UserMappingItemcategory");
        $userMappingItemcategoryImpl = UserMappingItemcategory::getInstance();
        $userCategorySQL = $userMappingItemcategoryImpl->getSpecialSQLStringByEmpNo($emp_no);
        // <- 2017-11-07 过滤物料

        import("@.ORG.Util.Sqlserver");
        $sqlObj = new Sqlserver();
        $where = '';
        $key = I('key');
        if ($key) {
            $key = iconv('utf-8', 'GBK', $key);
            $where = " and  t3.itemcode+t3.itemname like '%{$key}%'";
        }
        $where .= $userCategorySQL;
        //echo $where;die();
        //$where .= " and validFor = 'Y' ";

        $sql = "SELECT top 5000 t3.ItemName name ,ItemCode catid FROM OITM t3 WHERE ItemName is not null and ItemName !=''  $where ";
        $res = $sqlObj->query($sql);
        $data = array('total' => count($res), 'data' => $res);
        echo json_encode($data);
        exit;
    }

    function returnListType($type){
        $returnValue = "";
        switch ($type) {
            case '0':
                $returnValue = "普通";
                break;
            case '1':
                $returnValue = "活动";
                break;
            case '2':
                $returnValue = "集采";
                break;
        }
        return $returnValue;
    }

    function exportexcelsapbankrelation()
    {
        $code = trim($_REQUEST['code']);
        $start = $_REQUEST['start'];
        $end = $_REQUEST['end'];
        $stype = $_REQUEST['type'];

        $where = '1=1 and Active = 1 ';
        $emp_no = $_SESSION['emp_no'];

        if ($start) {
            $where .= " and think_sapbank_relation.create_time>=" . $start; //strtotime($start);
        }

        if ($end) {
            $where .= " and think_sapbank_relation.create_time <='" .substr($end,0,10) . " 23:59:59"."'" ; //strtotime($end . ' 23:59:59');
        }

        $where .= " and Customer_Code = '{$code}' and c.to_emp like '%{$emp_no}%' or c.emp_no like '%{$emp_no}%'";

        $model = M('sapbankRelation');
        $join = 'left join think_shelves_detailed as b on shelves_list_id = b.pid and shelves_detailed_id = b.id left join think_shelves_list as c on shelves_list_id = c.id';
        $filed = ("think_sapbank_relation.id as relation_id,b.id as inlist_id,think_sapbank_relation.*,b.*,c.to_emp");
        $data = $model->join($join)->field($filed)->where($where)->select();

        //Export to Excel:

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, 'OA物料号');
        $currentSheet->setCellValue('B'.$m, '上架类型');
        $currentSheet->setCellValue('C'.$m, '上架清单名');
        $currentSheet->setCellValue('D'.$m, '产品经理');
        $currentSheet->setCellValue('E'.$m, 'SAP物料号');

        $currentSheet->setCellValue('F'.$m, 'SAP物料名称');
        $currentSheet->setCellValue('G'.$m, '规格');
        $currentSheet->setCellValue('H'.$m, '平台商品代码');
        $currentSheet->setCellValue('I'.$m, '添加时间');
        $currentSheet->setCellValue('J'.$m, '主品物料号');

        $currentSheet->setCellValue('K'.$m, '颜色');
        $currentSheet->setCellValue('L'.$m, '毛利');
        $currentSheet->setCellValue('M'.$m, '商城价');
        $currentSheet->setCellValue('N'.$m, '毛利率');
        $currentSheet->setCellValue('O'.$m, '采购价');

        $currentSheet->setCellValue('P'.$m, '分期数');
        $currentSheet->setCellValue('Q'.$m, '分期费率(%)');
        $currentSheet->setCellValue('R'.$m, '预估运费');
        $currentSheet->setCellValue('S'.$m, '赠品价格');
        $currentSheet->setCellValue('T'.$m, '佣金');

        $currentSheet->setCellValue('U'.$m, '增值税等17%');
        $currentSheet->setCellValue('V'.$m, '包装费');
        $currentSheet->setCellValue('W'.$m, '返利金额');

        // Attachment begin
        $currentSheet->setCellValue('X'.$m, '天猫价');
        $currentSheet->setCellValue('Y'.$m, '天猫销量');
        $currentSheet->setCellValue('Z'.$m, '天猫');

        $currentSheet->setCellValue('AA'.$m, '京东价');
        $currentSheet->setCellValue('AB'.$m, '京东销量');
        $currentSheet->setCellValue('AC'.$m, '京东');

        $currentSheet->setCellValue('AD'.$m, '广发价');
        $currentSheet->setCellValue('AE'.$m, '广发销量');
        $currentSheet->setCellValue('AF'.$m, '广发');

        $currentSheet->setCellValue('AG'.$m, '建行价');
        $currentSheet->setCellValue('AH'.$m, '建行销量');
        $currentSheet->setCellValue('AI'.$m, '建行价');

        $currentSheet->setCellValue('AJ'.$m, '招行价');
        $currentSheet->setCellValue('AK'.$m, '招行销量');
        $currentSheet->setCellValue('AL'.$m, '招行');

        $currentSheet->setCellValue('AM'.$m, '交行价');
        $currentSheet->setCellValue('AN'.$m, '交行销量');
        $currentSheet->setCellValue('AO'.$m, '交行');

        //2017-07-10 new lines:
        $currentSheet->setCellValue('AP'.$m, '民生价');
        $currentSheet->setCellValue('AQ'.$m, '民生销量');
        $currentSheet->setCellValue('AR'.$m, '民生');

        $currentSheet->setCellValue('AS'.$m, '平安价');
        $currentSheet->setCellValue('AT'.$m, '平安销量');
        $currentSheet->setCellValue('AU'.$m, '平安');

        $currentSheet->setCellValue('AV'.$m, '中信价');
        $currentSheet->setCellValue('AW'.$m, '中信销量');
        $currentSheet->setCellValue('AX'.$m, '中信');

        $currentSheet->setCellValue('AY'.$m, '数量');
        //Attachment end

        $currentSheet->setCellValue('AZ'.$m, '备注');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['oa_id'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$this->returnListType($value['shelves_type']), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['shelves_list_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['shelves_list_pm'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['SAP_ItemCode'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['SAP_ItemName'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['BANK_Model'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$value['BANK_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$value['create_time'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$value['pcode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$value['color'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$value['maoli'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$value['mall_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$value['maolilv'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$value['purchase_price'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$value['by_stages'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$value['by_stages_rate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$value['freight'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$value['gift_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$value['bank_commission'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$value['zengzhishui'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$value['packing_charge'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$value['rebate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            // Attachment begin
            $currentSheet->setCellValueExplicit('X'.$cellIndex,$value['tamll_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Y'.$cellIndex,$value['tamll_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Z'.$cellIndex,$value['tamll'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AA'.$cellIndex,$value['jd_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AB'.$cellIndex,$value['jd_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AC'.$cellIndex,$value['jd'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AD'.$cellIndex,$value['guangfa_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AE'.$cellIndex,$value['guangfa_S_AMT'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AF'.$cellIndex,$value['guangfa'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AG'.$cellIndex,$value['jianhang_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AH'.$cellIndex,$value['jianhang_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AI'.$cellIndex,$value['jianhang'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AJ'.$cellIndex,$value['zhaohang_price'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AK'.$cellIndex,$value['zhaohang_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AL'.$cellIndex,$value['zhaohang'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AM'.$cellIndex,$value['jiaohang_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AN'.$cellIndex,$value['jiaohang_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AO'.$cellIndex,$value['jiaohang'],\PHPExcel_Cell_DataType::TYPE_STRING);

            //2017-07-10 new lines:
            $currentSheet->setCellValueExplicit('AP'.$cellIndex,$value['minsheng_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AQ'.$cellIndex,$value['minsheng_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AR'.$cellIndex,$value['minsheng'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AS'.$cellIndex,$value['pingan_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AT'.$cellIndex,$value['pingan_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AU'.$cellIndex,$value['pingan'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AV'.$cellIndex,$value['zhongxin_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AW'.$cellIndex,$value['zhongxin_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AX'.$cellIndex,$value['zhongxin'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AY'.$cellIndex,$value['qty'],\PHPExcel_Cell_DataType::TYPE_STRING);
            // Attachment end

            $currentSheet->setCellValueExplicit('AZ'.$cellIndex,$value['remark'],\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    function queryItemByCode()
    {
        $code = trim(I('code'));
        $start = I('start');
        $end = I('end');
        $stype = I('type');
        $where = '1=1 and Active = 1 ';

        $emp_no = $_SESSION['emp_no'];

        if ($start) {
            $where .= " and think_sapbank_relation.create_time>=" . $start; //strtotime($start);
        }

        if ($end) {
            $where .= " and think_sapbank_relation.create_time <='" .substr($end,0,10) . " 23:59:59"."'" ; //strtotime($end . ' 23:59:59');
        }

//        if ($stype) {
//            $checkdate = date('Y-m-d H:i:s');
//            if ($stype == 1) {
//                $where .= " and BANK_ToTime >'{$checkdate}'";
//            } else {
//                $where .= " and BANK_ToTime <'{$checkdate}'";
//            }
//        }

        $tmpWhere = $where." and Customer_Code = '{$code}' ";

        $where .= " and Customer_Code = '{$code}' and c.to_emp like '%{$emp_no}%'";//or c.emp_no = '{$emp_no}'

        $where .= " or (".$tmpWhere." and c.emp_no = '{$emp_no}')";

        $model = M('sapbankRelation');
        $join = 'left join think_shelves_detailed as b on shelves_list_id = b.pid and shelves_detailed_id = b.id left join think_shelves_list as c on shelves_list_id = c.id';
        $filed = ("think_sapbank_relation.id as relation_id,b.id as inlist_id,think_sapbank_relation.*,b.*,c.to_emp");
        $pageIndex = I('pageIndex') + 1;
        $pageSize = I('pageSize');
        $_POST['p'] = $pageIndex;
        $count = $model->join($join)->field($filed)->where($where)->count();
        import("@.ORG.Util.Page");
        $p = new \Page($count, $pageSize);
        //分页查询数据
        $res = $model->join($join)->field($filed)->where($where)->limit($p->firstRow . ',' . $p->listRows)->select();
        foreach ($res as $key => $val) {
            $res[$key]['create_time'] = substr($res[$key]['create_time'],0,16);
        }
        $data = array('total' => $count, 'data' => $res);
        echo json_encode($data);
        exit;
    }

    /**
     * 单个添加修改
     */
    function saveRelation()
    {
        $respone = array('code' => 0, 'msg' => 'ok');
        $res = 0;
        $postData = I('data');
        $rowData = json_decode($postData, 1);
        $respone = $this->dosaveRelation($rowData);
        echo json_encode($respone, JSON_UNESCAPED_UNICODE);
        exit;
    }

    function dosaveRelation($rowData)
    {
        $respone = array('code' => 0, 'msg' => '');
        if (!$this->checkDate($rowData['BANK_FroTime'], $rowData['BANK_ToTime'])) {
            $respone['code'] = -1;
            $respone['msg'] = '时间设置错误';
            return $respone;

        }

        if (!isset($rowData['Customer_Code']) || !$rowData['Customer_Code']) {
            $respone['code'] = -1;
            $respone['msg'] = '客户代码不能为空';
            return $respone;
        }


        unset($rowData['_id'], $rowData['_uid'], $rowData['_status']);
        $mode = M('sapbankRelation');

        // 检测物料号是否重复

        // 2017-07-04 “多套餐”模式: 同一主品 + 不同赠品 增加 “规格”键 约束(需修改逻辑).
        // OR
        // 2017-07-04 “多套餐”模式: 返回的 套餐 平台商品代码 唯一(不需修改逻辑).
        //$check = $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and SAP_ItemCode='{$rowData['SAP_ItemCode']}'")->find();
        /*$check = $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$rowData['BANK_ItemCode']}' and SAP_ItemCode = '{$rowData['SAP_ItemCode']}'")->find();
        if ($check) {
            $respone['code'] = -1;
            $respone['msg'] = $rowData['SAP_ItemCode'] . '物料重复！';
            return $respone;
        }*/

        // 2017-07-17 2017-07-14会议确认："平台代码 + 平台商品代码 + SAP物料号" 会出现合理重复.
        // 2017-07-17 更新 物料号重复验证 规则
        // 2017-07-18 只校验 主品
        // 2017-08-18 增加BANK_Model约束条件，BANK_Model默认值''，此次更新解决同一平台代码对应多条SAP物料号(以规格BANK_Model区分)
        // 2017-08-22 因为BANK_Model表默认值为'无'，所以在判断之前修改提交数据 $rowData['BANK_Model']

        $pBANK_ItemCode = trim($rowData['BANK_ItemCode']);
        $pBANK_Model = trim($rowData['BANK_Model']);

        if(empty($pBANK_Model))
        {
            $pBANK_Model = '无';
        }
        $check = $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$pBANK_ItemCode}' and SAP_PItemCode = '' and BANK_Model ='{$pBANK_Model}'")->find();
        if ($check)
        {
            if ($check['SAP_ItemCode'] == $rowData['SAP_ItemCode'])
            {
                // 允许情况,入库
            }
            else
            {
                if (empty($rowData['pcode'])) {
                    // 非赠品
                    $respone['code'] = -1;
                    $respone['msg'] = $rowData['SAP_ItemCode'] . '物料重复！先下架物料再上架。';
                    return $respone;
                }
            }

        }

        // 2017-06-21 需求更新：赠品由产品部添加，导致此处逻辑判断变更，添加新字段 SAP_PItemCode 判断，更新主品以及相关赠品状态
        // 将旧的对应关系设置为非激活: Active = 0;
        if (empty($rowData['pcode'])) {
            // 2017-07-17 没有自动下架概念，所有在架物料的下架都应是人为操作。
            // 以下状态更新应只发生在提交的物料非赠品的情况下
            /*
            $check = $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$rowData['BANK_ItemCode']}' and SAP_PItemCode=''")->find();
            if ($check) {
                $saveData = array();
                $saveData['Active'] = 0;
                // 更新主品状态
                $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$rowData['BANK_ItemCode']}' and SAP_PItemCode=''")->save($saveData);
                // 更新赠品状态
                $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$rowData['BANK_ItemCode']}' and SAP_PItemCode='{$check['SAP_ItemCode']}'")->save($saveData);
            }
            */
        }
        else{
            // 2017-06-27赠品逻辑校验
            // 找出库内赠品 (注意如果有多个旧清单)
            $checkList = $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$pBANK_ItemCode}' and SAP_PItemCode = '{$rowData['pcode']}'")->select();
            if (is_array($checkList)) {
                foreach ($checkList as $rKey => $rValue) {
                    //if ($checkList) {
                        // 校验清单名
                        if ($rValue['shelves_list_name'] == $rowData['list_name']) {
                            // 同一清单: go on
                        } else {
                            // 新清单: 更新旧清单数据 (规则:旧的赠品全部下架,新清单内的赠品上架)
                            $saveData = array();
                            $saveData['Active'] = 0;
                            // 更新旧清单赠品状态
                            $mode->where("`Customer_Code` = '{$rowData['Customer_Code']}' and BANK_ItemCode='{$pBANK_ItemCode}' and SAP_PItemCode='{$rowData['pcode']}' and shelves_list_name='{$rValue['shelves_list_name']}'")->save($saveData);
                        }
                    //}
                }
            }
            else{
                // 不存在: go on
            }

        }

        // 一切正常后保存数据
        $newData['oa_id'] = $rowData['oa_id'];
        $newData['Customer_Code'] = $rowData['Customer_Code'];
        $newData['SAP_ItemCode'] = $rowData['SAP_ItemCode'];
        $newData['SAP_ItemName'] = $rowData['SAP_ItemName'];
        $newData['BANK_ItemCode'] = $pBANK_ItemCode;
        $newData['BANK_Model'] = $pBANK_Model;
        $newData['BANK_Gift'] = $rowData['BANK_Gift'];
        $newData['BANK_FroTime'] = $rowData['BANK_FroTime'];
        $newData['BANK_ToTime'] = $rowData['BANK_ToTime'];
        $newData['create_time'] = date('Y-m-d H:i:s'); //time();
        // 2017-06-19 增加保存 上架清单号:shelves_list_id , 上架清单内Item ID号:shelves_detailed_id, 上架类型: shelves_type
        $newData['shelves_list_id'] = $rowData['pid'];
        $newData['shelves_detailed_id'] = $rowData['id'];
        $newData['shelves_type'] = $rowData['type'];
        // 2017-06-20 增加保存 上架清单名, 产品经理
        $newData['shelves_list_name'] = $rowData['list_name'];
        $newData['shelves_list_pm'] = $rowData['product_m'];
        // 2017-06-21 增加保存 主品ID
        $newData['SAP_PItemCode'] = $rowData['pcode'];
        // 2017-07-31 记录操作人信息
        $emp_no = $_SESSION['emp_no'];
        $newData['operatorname'] = $emp_no;

        $mode->add($newData);

        //2017-06-13会议更新需求：一次发起可多次上架，所以不再更新 think_shelves_detailed 中的 status
        //2017-06-15更新需求：改回原方式，每次上架都需要重新发起，所以需要更新 think_shelves_detailed 中的 status
        //更新 think_shelves_detailed 中的 status
        //2017-07-04根据2017-06-30会议内容，考虑产品池的需求扩展 think_shelves_detailed 中的 status，更新为：'状态 0默认 1通知发出 2上架 3下架'
        $modelShelvesDetailed = M("ShelvesDetailed");
        $where = " 1 = 1 ";
        $where .= "and pid =  '{$rowData['pid']}' and code = '{$rowData['SAP_ItemCode']}' and oa_id = '{$rowData['oa_id']}' and id = {$rowData['id']}";
        $shelvesDetailedSaveData = array();
        $shelvesDetailedSaveData['status'] = 3; //状态: 0默认 1通知发出 2提报 3上架 4下架
        $shelvesDetailedSaveData['online_time'] = date('Y-m-d H:i:s');
        $modelShelvesDetailed->where($where)->save($shelvesDetailedSaveData);

//        if(isset($newData['id'])){ //有id 则为从上架通知获取的物料信息
//         ShelvesViewModel::updateDetailed($rowData['U_ItemCode'],$rowData['id']); //上架通知物料修改状态
//        }

        return $respone;
    }

    function checkDate($date, $date2)
    {
        $time = strtotime($date);
        $time2 = strtotime($date2);
        if ($time > $time2) {
            return false;
        }
        return true;

    }

    function importRelation()
    {
        $respone = array('code' => 0, 'msg' => '');
        $postData = json_decode(I('data'), 1);
        $list = $postData['data'];
        $msg = '操作成功<br/>';
        foreach ($list as $key => $val) {
            $val['Customer_Code'] = $postData['Customer_Code'];
            unset($val['check']);
            $res = $this->dosaveRelation($val);
            if ($res['code']) {
                $msg .= $res['msg'] . '<br/>';
            }
        }
        $respone['msg'] = $msg;
        echo json_encode($respone, JSON_UNESCAPED_UNICODE);
        exit;
    }

    function togift()
    {
        $gid = I('gid');
        if (!$gid) {
            $this->error('主品信息错误');
            exit;
        }
        $this->assign('src', '/sap/goods_gift.php?gid=' . $gid);
        $this->display('report/index');
    }

    function savegift()
    {
        $respone = array('code' => 0, 'msg' => 'ok');
        $res = 0;
        $postData = I('data');
        $rowData = json_decode($postData, 1);

        //整理数据
        unset($rowData['_id'], $rowData['_uid'], $rowData['_status']);
        unset($rowData['code'], $rowData['name'], $rowData['color']);

        $goodsgiftItemModel = M('goodsgiftItem');
        if (!isset($rowData['id'])) {
            $check = $goodsgiftItemModel->where("gtime_id = '{$rowData['gtime_id']}' and gitem_id='{$rowData['gitem_id']}'")->find();
            if ($check) {
                $respone['code'] = -1;
                $respone['msg'] = '不能重复添加赠品';
                echo json_encode($respone, JSON_UNESCAPED_UNICODE);
                exit;
            }
        }

        if (isset($rowData['id'])) {
            $respone['code'] = $goodsgiftItemModel->where("id={$rowData['id']}")->save($rowData);
        } else
            $respone['code'] = $goodsgiftItemModel->add($rowData);
        echo json_encode($respone, JSON_UNESCAPED_UNICODE);
        exit;
    }

    /**
     * 物料赠品时间段
     */
    function savegifttime()
    {
        $respone = array('code' => 0, 'msg' => 'ok');
        $res = 0;
        $postData = I('data');
        $rowData = json_decode($postData, 1);
        if (!$this->checkDate($rowData['U_FroTime'], $rowData['U_ToTime'])) {
            $respone['code'] = -1;
            $respone['msg'] = '时间设置错误';
            echo json_encode($respone, JSON_UNESCAPED_UNICODE);
            exit;
        }
        unset($rowData['_id'], $rowData['_uid'], $rowData['_status']);
        //$mode = $this->M2('GiftTime');
        $goodsgiftTimeModel = M('goodsgiftTime');
        if (!isset($rowData['id'])) {
            $check = $goodsgiftTimeModel->where("U_FroTime = '{$rowData['U_FroTime']}' and U_ToTime='{$rowData['U_ToTime']}' and U_RelationId={$rowData['U_RelationId']}")->find();
            if ($check) {
                $respone['code'] = -1;
                $respone['msg'] = '时间段不能重复';
                echo json_encode($respone, JSON_UNESCAPED_UNICODE);
                exit;
            }
        }

        if (isset($rowData['id'])) {
            $respone['code'] = $goodsgiftTimeModel->where("id={$rowData['id']}")->save($rowData);
        } else
            $respone['code'] = $goodsgiftTimeModel->add($rowData);
        echo json_encode($respone, JSON_UNESCAPED_UNICODE);
        exit;
    }

    function querygift()
    {
        $rid = I('rid');
        $where = " gtime_id={$rid} ";
        //$model = $this->M2('GiftItem'); => think_goodsgift_item
        $goodsgiftItemModel = M('goodsgiftItem');
        $pageIndex = I('pageIndex') + 1;
        $pageSize = I('pageSize');
        $_POST['p'] = $pageIndex;
        $count = $goodsgiftItemModel->where($where)->count();
        import("@.ORG.Util.Page");
        $p = new \Page($count, $pageSize);
        //分页查询数据
        $res = $goodsgiftItemModel->where($where)->order()->limit($p->firstRow . ',' . $p->listRows)->select();

        $data = array('total' => $count, 'data' => $res);
        echo json_encode($data);
        exit;
    }

    /**
     * 赠品时间段列表
     */
    function querygiftTime()
    {
        $rid = I('rid');
        $where = " U_RelationId={$rid} ";
        //$model = $this->M2('GiftTime');
        $goodsGiftTimeModel = M('goodsgiftTime');
        $pageIndex = I('pageIndex') + 1;
        $_POST['p'] = $pageIndex;
        $pageSize = I('pageSize');
        $count = $goodsGiftTimeModel->where($where)->count();
        import("@.ORG.Util.Page");
        $p = new \Page($count, $pageSize);
        //分页查询数据
        $res = $goodsGiftTimeModel->where($where)->order()->limit($p->firstRow . ',' . $p->listRows)->select();
        $data = array('total' => $count, 'data' => $res);
        echo json_encode($data);
        exit;
    }

    function import()
    {
        $this->assign('src', '/sap/goods_import.php');
        $this->display('report/index');
    }

    function noticed()
    {
        $this->assign('src', '/sap/goods_noticed.php');
        $this->display('report/index');
    }


    function importAjax()
    {
        $time =time();
        // excel 表头 与 mysql 表头对应关系
        $orderHeadArray = array(
            'SAP物料编号' =>'SAP_ItemCode', //A
            'SAP物料名称' =>'SAP_ItemName', //B
            '规格'=>'BANK_Model', //C
            '客户代码'=>'Customer_Code', //D
            '平台商品代码'=>'BANK_ItemCode', //E
            '上架时间'=>'BANK_FroTime', //F
            '下架时间'=>'BANK_ToTime', //G
        );

        $File = D('File');
        $file_driver = C('DOWNLOAD_UPLOAD_DRIVER');
        $info = $File->upload($_FILES, C('DOWNLOAD_UPLOAD'), C('DOWNLOAD_UPLOAD_DRIVER'), C("UPLOAD_{$file_driver}_CONFIG"));
        if ($info) {
            //取得成功上传的文件信息
            Vendor('Excel.PHPExcel');
            //导入thinkphp第三方类库
            $inputFileName = C('DOWNLOAD_UPLOAD.rootPath') . $info['uploadfile']["savepath"] . $info['uploadfile']["savename"];
            $objPHPExcel = \PHPExcel_IOFactory::load($inputFileName);
            $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
            $count = count($sheetData);

            // excel 中表头
            $excelHeadCN = $sheetData[1];
            $excelHead = array();
            foreach ($excelHeadCN as $key => $value){
                $excelHead[$key] = $orderHeadArray[$value];
            }
            unset($excelHead['']);
            foreach ($excelHead as $key => $value)
            {
                if (empty($value)){
                    unset($excelHead[$key]);
                }
            }
            // Table表头与Excel列对应关系
            //var_dump($excelHead);
            unset($sheetData[1]); //删除excel表头 列名等行数据

            $mergeStr = '';
            $countNum = 0;
            foreach ($sheetData as $key => $v) {
                //数据 $key行号 $v数组

                $targetData = array();
                foreach ($v as $key => $value)
                {
                    //Core
                    $targetData[$excelHead[$key]] = trim($value);
                }
                unset($targetData['']);

                // 补全数据
                $targetData['import_type'] = 1;
                $targetData['ctime'] = $time;

                $str = "'".implode("','", $targetData)."'";

                $countNum++;

                if ($countNum == 1) $mergeStr .= "({$str})";
                else $mergeStr .= ", ({$str})";

            }
            $sql =" insert into think_sapbank_relation  (" .implode(',', array_keys($targetData) ) .") values $mergeStr";
            //echo $sql;
            //die();

            $sapbankRelationModel = M('sapbankRelation');
            $result = $sapbankRelationModel->execute($sql);
            $return = 0;
            $errorStr = '导入不成功!';
            if($result){
                $return = 1;
                $errorStr = '';
            }
            //$return = $this->unique_arr($return, true);//去重复
            echo json_encode(array('data' => $return, 'msg' => $errorStr), 1);

        }
    }


    /**
     * 检查物料编号是否存在
     * @param unknown $code
     */
    function checkItemCode($code)
    {

        $sql = "SELECT count(1) AS total FROM mallbuilder_product AS a LEFT JOIN mallbuilder_product_setmeal AS b ON a.id = b.pid where a.`code`='{$code}' or b.sku='{$code}'";
        $model = $this->M2('product');
        $res = $model->query($sql);
        return $res[0]['total'];
    }


    /**
     * 简单处理获取excel时间格式问题
     * @param unknown $date
     * @return string
     */
    /*   private function excelTime($date) {
          error_log($date);exit;
          $newStr = date('Y').'-'.substr($date, 0,strlen($date)-3);
          error_log($newStr);exit;
          return $newStr;


      } */

    /**
     * 导入的数据去重复
     * @param unknown $array2D
     * @param string $stkeep
     * @param string $ndformat
     * @return multitype:
     */
    function unique_arr($array2D, $stkeep = false, $ndformat = true)
    {
        // 判断是否保留一级数组键 (一级数组键可以为非数字)
        if ($stkeep) $stArr = array_keys($array2D);

        // 判断是否保留二级数组键 (所有二级数组键必须相同)
        if ($ndformat) $ndArr = array_keys(end($array2D));

        //降维,也可以用implode,将一维数组转换为用逗号连接的字符串
        foreach ($array2D as $v) {
            $v = join(",", $v);
            $temp[] = $v;
        }

        //去掉重复的字符串,也就是重复的一维数组
        $temp = array_unique($temp);

        //再将拆开的数组重新组装
        foreach ($temp as $k => $v) {
            if ($stkeep) $k = $stArr[$k];
            if ($ndformat) {
                $tempArr = explode(",", $v);
                foreach ($tempArr as $ndkey => $ndval) $output[$k][$ndArr[$ndkey]] = $ndval;
            } else $output[$k] = explode(",", $v);
        }

        $new = array();
        foreach ($output as $val) {
            $new[] = $val;
        }
        return $new;
    }

    function getCatAjax()
    {
        $type = I('type');//获取分类类型
        $model = $this->M2('ProductCat');
        $where = '';
        switch ($type) {
            case 1:
                $where = 'catid<9999';
                break;
            case 2:
            case 3:
                $s = I("pcatid") . "00";
                $b = I("pcatid") . "99";
                $where = " catid>'$s' and catid<'$b' ";

                break;
        }

        $list = $model->where($where)->order('nums asc')->select();
        echo json_encode($list);

    }

    function queryItemBygoodssearchOA()
    {
        //get items from OA DB, table: think_sap_items
        $modelSapItems = M('SapItems');
        $where = '1 = 1 ';
        $key = I('key');
        if ($key) {
            //$filed = "item_name as name, sap_code as code, color, item_id as oa_id";
            //$where .= " and CONCAT(item_name, sap_code) like '%{$key}%'";
            //$result = $modelSapItems->field($filed)->where($where)->select();

            $quersql = "SELECT a.weight,  a.item_name as name, a.sap_code as code, a.color, a.item_id as true_oa_id, a.OriginalPurchasePrice as purchase_price ,b.* from  think_sap_items as a LEFT JOIN think_sap_item_attachment as b on a.item_id=b.oa_id WHERE 1=1 and sap_code != ''  and CONCAT(item_name, sap_code) like '%{$key}%'";
            $qResult =  M('SapItems')->query($quersql);
            if(is_array($qResult)) {
                $result = $qResult;
            }else
            {
                return;
            }

            $data = array('total' => count($result), 'data' => $result);
            echo json_encode($data);
        }
        exit;
    }

    function queryItemBygoodssearch()
    {

        /*
        $key = I('key');
        $where = 'where 1 =1 ';
        if($key){
            $where.= " and CONCAT(a.`name`,a.`code`) like '%{$key}%' ";
        }
        
        $catId = I('catid');
        if($catId){
            $where.="and catid = {$catId}";
        }
        
        $brand = I('brand');
        if($brand){
            $where.="and brand like '%{$brand}%'";
        }
        $model =$this->M2('product');
        $pageIndex = I('pageIndex');
        $pageSize = I('pageSize');
        $countsql = "SELECT count(1) as total from  mallbuilder_product as a LEFT JOIN mallbuilder_product_setmeal as b on a.id=b.pid {$where}";
        $limit = ' limit '.($pageIndex*$pageSize).','.$pageSize;
        $quersql = "SELECT  a.id,a.keywords,`code`,sku,`name`,setmeal,brand,pic,weight,cubage from  mallbuilder_product as a LEFT JOIN mallbuilder_product_setmeal as b on a.id=b.pid {$where} order by a.id desc {$limit} ";
       // echo $quersql;exit;
        $total = $model->query($countsql)[0]['total'];
        $res = $model->query($quersql);
        $newData = array();
        foreach ($res as $key=>$item){
            $newData[$key]['id']=$item['id'];
            $newData[$key]['tag']=$item['keywords'];
            $newData[$key]['weight']=$item['cubage'];
            $newData[$key]['img']=$item['pic'];
            $newData[$key]['color']=$item['setmeal'];
            if(!$item['sku'])
                $newData[$key]['code']=$item['code'];
            else
                $newData[$key]['code']=$item['sku'];
            $newData[$key]['name']=$item['name'].' '.$item['setmeal'];
            $newData[$key]['U_TypeOption']=$item['setmeal'];
        }
        $data = array('total'=>$total,'data'=>$newData);

        echo json_encode($data);
        exit;
        */

        // 无分页, 前面5000条
        import("@.ORG.Util.Sqlserver");
        $sqlObj = new Sqlserver();
        $where = '';
        $key = I('key');
        if ($key) {
            $key = iconv('utf-8', 'GBK', $key);
            $where = " and  t3.itemcode+t3.itemname like '%{$key}%'";
        }

        if ($key) {
            $sql = "SELECT top 150 t3.ItemName name ,ItemCode code, U_Color color FROM OITM t3 WHERE ItemName is not null and ItemName !=''  $where ";
            $res = $sqlObj->query($sql);
            $data = array('total' => count($res), 'data' => $res);
            echo json_encode($data);
        }else{}

        exit;

    }

    function subStr()
    {
        $str = '                                                                                                 ';
        if (substr($str, 1, 1) != ' ') {
            echo 1111;
        }

    }

    /**
     * 生成数据包
     */
    function download()
    {
        $dataModel = I('model');//下载模版
        $geshi = I('type');//下载格式
        $list = rtrim(I('data'), ',');//物料编号
        $list = explode(',', $list);
        $fileInfo = $this->doDownload($dataModel, $geshi, $list);
        echo json_encode($fileInfo);
        exit();
    }

    /**
     * 生成数据文件
     * @param unknown $model
     * @param unknown $type
     * @param unknown $list
     */
    private function doDownload($model, $type, $list)
    {

        return $this->createText($model, $list, $type);
    }


    private function createText($model, $list, $type = 'txt')
    {
        $PATH = '/Uploads/Download/data/' . date('Ymd') . '/';
        if (!file_exists($PATH)) {
            mkdir(WWWROOT . $PATH);
        }

        $datamodel = C('DOWNLOADMODEL')[$model];
        $txt_str = '';
        foreach ($list as $code) {
            $temp = '';
            $item_temp = $this->getItemMsg($code);
            if (!$item_temp) continue;

            foreach ($datamodel['style'] as $key => $v) {
                $temp .= str_pad($item_temp[$v['cname']], $v['lenght'], ' ', STR_PAD_RIGHT) . $datamodel['interval'];
            }
            $txt_str .= rtrim($temp, $datamodel['interval']) . "\n";
            unset($temp);

        }
        $filename = substr(md5(time() . ''), 8, 16);
        $file_suffix = '.txt';
        $fp = fopen(WWWROOT . $PATH . $filename . $file_suffix, "w");  //fopen  中文文件名
        fwrite($fp, $txt_str);
        fclose($fp);
        if ($type == 'zip') {
            $this->zip(WWWROOT . $PATH . $filename . '.zip', WWWROOT . $PATH . $filename . $file_suffix);
        }
        $respons = array();
        $respons['path'] = $PATH;
        $respons['file_name'] = $filename . '.' . $type;
        return $respons;

    }

    function getItemMsg($code)
    {
        $model = $this->M2('product');
        $sql = "SELECT  * from  mallbuilder_product as a LEFT JOIN mallbuilder_product_setmeal as b on a.id=b.pid  WHERE sku='{$code}' or `code`='{$code}' LIMIT 1";
        $res = $model->query($sql)[0];
        return $res ?: false;
    }

    function zip($filename = '', $files)
    {
        $zip = new \ZipArchive();
        if ($zip->open($filename, \ZIPARCHIVE::CREATE) !== TRUE) {
            return false;
        }

        if (file_exists($files)) {
            $zip->addFile($files, basename($files));//第二个参数是放在压缩包中的文件名称，如果文件可能会有重复，就需要注意一下
        }

        $res = $zip->numFiles;
        $zip->close();

        return $res;
    }

    function xiajia()
    {
        session('u_code', null);
        $this->assign('src', '/sap/xiajia.php');
        $this->display('report/index');
    }

    function queryItemByCodeXiaJia()
    {
        $code = trim(I('code'));
        $start = I('start');
        $end = I('end');
        $stype = I('type');
        $where = '1=1 and Active = 1 ';

        $emp_no = $_SESSION['emp_no'];
        // 验证权限
        $sapbankWatchModel = M("sapbankWatch");
        $watchEnable = $sapbankWatchModel->where("emp_no='{$emp_no}'")->find();
        if($watchEnable){
            $customerCodeList = explode(',',$watchEnable['Customer_Code']);
            if (in_array($code,$customerCodeList)){
                // go on
            }else{
                return;
            }
        }
        else{
            return;
        }

        if ($start) {
            $where .= " and think_sapbank_relation.create_time>=" . $start; //strtotime($start);
        }

        if ($end) {
            $where .= " and think_sapbank_relation.create_time <='" .substr($end,0,10) . " 23:59:59"."'" ; //strtotime($end . ' 23:59:59');
        }

        $where .= " and Customer_Code = '{$code}'"; //and c.to_emp like '%{$emp_no}%' or c.emp_no like '%{$emp_no}%'

        $model = M('sapbankRelation');
        $join = 'left join think_shelves_detailed as b on shelves_list_id = b.pid and shelves_detailed_id = b.id left join think_shelves_list as c on shelves_list_id = c.id';
        $filed = ("think_sapbank_relation.id as relation_id,b.id as inlist_id,think_sapbank_relation.*,b.*,c.to_emp");
        $pageIndex = I('pageIndex') + 1;
        $pageSize = I('pageSize');
        $_POST['p'] = $pageIndex;
        $count = $model->join($join)->field($filed)->where($where)->order("think_sapbank_relation.id desc")->count();
        import("@.ORG.Util.Page");
        $p = new \Page($count, $pageSize);
        //分页查询数据
        $res = $model->join($join)->field($filed)->where($where)->order("think_sapbank_relation.id desc")->limit($p->firstRow . ',' . $p->listRows)->select();
        foreach ($res as $key => $val) {
            //$res[$key]['create_time'] = date('Y-m-d' ,$res[$key]['create_time']); //date('Y-m-d', $res[$key]['ctime']);
            $res[$key]['create_time'] = substr($res[$key]['create_time'],0,16);
        }
        $data = array('total' => $count, 'data' => $res);
        echo json_encode($data);
        exit;
    }

    function exportexcelsapbankrelationXiaJia()
    {
        $code = trim($_REQUEST['code']);
        $start = $_REQUEST['start'];
        $end = $_REQUEST['end'];
        $stype = $_REQUEST['type'];

        $where = '1=1 and Active = 1 ';
        $emp_no = $_SESSION['emp_no'];
        // 验证权限
        $sapbankWatchModel = M("sapbankWatch");
        $watchEnable = $sapbankWatchModel->where("emp_no='{$emp_no}'")->find();
        if($watchEnable){
            $customerCodeList = explode(',',$watchEnable['Customer_Code']);
            if (in_array($code,$customerCodeList)){
                // go on
            }else{
                return;
            }
        }
        else{
            return;
        }

        if ($start) {
            $where .= " and think_sapbank_relation.create_time>=" . $start; //strtotime($start);
        }

        if ($end) {
            $where .= " and think_sapbank_relation.create_time <='" .substr($end,0,10) . " 23:59:59"."'" ; //strtotime($end . ' 23:59:59');
        }

        $where .= " and Customer_Code = '{$code}'"; //and c.to_emp like '%{$emp_no}%' or c.emp_no like '%{$emp_no}%'

        $model = M('sapbankRelation');
        $join = 'left join think_shelves_detailed as b on shelves_list_id = b.pid and shelves_detailed_id = b.id left join think_shelves_list as c on shelves_list_id = c.id';
        $filed = ("think_sapbank_relation.id as relation_id,b.id as inlist_id,think_sapbank_relation.*,b.*,c.to_emp");
        $data = $model->join($join)->field($filed)->where($where)->select();

        //Export to Excel:

        Vendor('Excel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, 'OA物料号');
        $currentSheet->setCellValue('B'.$m, '上架类型');
        $currentSheet->setCellValue('C'.$m, '上架清单名');
        $currentSheet->setCellValue('D'.$m, '产品经理');
        $currentSheet->setCellValue('E'.$m, 'SAP物料号');

        $currentSheet->setCellValue('F'.$m, 'SAP物料名称');
        $currentSheet->setCellValue('G'.$m, '规格');
        $currentSheet->setCellValue('H'.$m, '平台商品代码');
        $currentSheet->setCellValue('I'.$m, '添加时间');
        $currentSheet->setCellValue('J'.$m, '主品物料号');

        $currentSheet->setCellValue('K'.$m, '颜色');
        $currentSheet->setCellValue('L'.$m, '毛利');
        $currentSheet->setCellValue('M'.$m, '商城价');
        $currentSheet->setCellValue('N'.$m, '毛利率');
        $currentSheet->setCellValue('O'.$m, '采购价');

        $currentSheet->setCellValue('P'.$m, '分期数');
        $currentSheet->setCellValue('Q'.$m, '分期费率(%)');
        $currentSheet->setCellValue('R'.$m, '预估运费');
        $currentSheet->setCellValue('S'.$m, '赠品价格');
        $currentSheet->setCellValue('T'.$m, '佣金');

        $currentSheet->setCellValue('U'.$m, '增值税等17%');
        $currentSheet->setCellValue('V'.$m, '包装费');
        $currentSheet->setCellValue('W'.$m, '返利金额');

        // Attachment begin
        $currentSheet->setCellValue('X'.$m, '天猫价');
        $currentSheet->setCellValue('Y'.$m, '天猫销量');
        $currentSheet->setCellValue('Z'.$m, '天猫');

        $currentSheet->setCellValue('AA'.$m, '京东价');
        $currentSheet->setCellValue('AB'.$m, '京东销量');
        $currentSheet->setCellValue('AC'.$m, '京东');

        $currentSheet->setCellValue('AD'.$m, '广发价');
        $currentSheet->setCellValue('AE'.$m, '广发销量');
        $currentSheet->setCellValue('AF'.$m, '广发');

        $currentSheet->setCellValue('AG'.$m, '建行价');
        $currentSheet->setCellValue('AH'.$m, '建行销量');
        $currentSheet->setCellValue('AI'.$m, '建行价');

        $currentSheet->setCellValue('AJ'.$m, '招行价');
        $currentSheet->setCellValue('AK'.$m, '招行销量');
        $currentSheet->setCellValue('AL'.$m, '招行');

        $currentSheet->setCellValue('AM'.$m, '交行价');
        $currentSheet->setCellValue('AN'.$m, '交行销量');
        $currentSheet->setCellValue('AO'.$m, '交行');

        //2017-07-10 new lines:
        $currentSheet->setCellValue('AP'.$m, '民生价');
        $currentSheet->setCellValue('AQ'.$m, '民生销量');
        $currentSheet->setCellValue('AR'.$m, '民生');

        $currentSheet->setCellValue('AS'.$m, '平安价');
        $currentSheet->setCellValue('AT'.$m, '平安销量');
        $currentSheet->setCellValue('AU'.$m, '平安');

        $currentSheet->setCellValue('AV'.$m, '中信价');
        $currentSheet->setCellValue('AW'.$m, '中信销量');
        $currentSheet->setCellValue('AX'.$m, '中信');

        $currentSheet->setCellValue('AY'.$m, '数量');
        //Attachment end

        $currentSheet->setCellValue('AZ'.$m, '备注');

        $cellIndex = 1;
        foreach ($data as $key => $value){
            $cellIndex++;
            $currentSheet->setCellValueExplicit('A'.$cellIndex,$value['oa_id'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$this->returnListType($value['shelves_type']), \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$value['shelves_list_name'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$value['shelves_list_pm'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$value['SAP_ItemCode'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$value['SAP_ItemName'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$value['BANK_Model'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$value['BANK_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$value['create_time'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$value['pcode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$value['color'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$value['maoli'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$value['mall_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$value['maolilv'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$value['purchase_price'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$value['by_stages'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$value['by_stages_rate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$value['freight'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$value['gift_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$value['bank_commission'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$value['zengzhishui'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$value['packing_charge'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$value['rebate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            // Attachment begin
            $currentSheet->setCellValueExplicit('X'.$cellIndex,$value['tamll_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Y'.$cellIndex,$value['tamll_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Z'.$cellIndex,$value['tamll'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AA'.$cellIndex,$value['jd_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AB'.$cellIndex,$value['jd_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AC'.$cellIndex,$value['jd'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AD'.$cellIndex,$value['guangfa_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AE'.$cellIndex,$value['guangfa_S_AMT'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AF'.$cellIndex,$value['guangfa'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AG'.$cellIndex,$value['jianhang_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AH'.$cellIndex,$value['jianhang_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AI'.$cellIndex,$value['jianhang'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AJ'.$cellIndex,$value['zhaohang_price'],\PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AK'.$cellIndex,$value['zhaohang_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AL'.$cellIndex,$value['zhaohang'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AM'.$cellIndex,$value['jiaohang_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AN'.$cellIndex,$value['jiaohang_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AO'.$cellIndex,$value['jiaohang'],\PHPExcel_Cell_DataType::TYPE_STRING);

            //2017-07-10 new lines:
            $currentSheet->setCellValueExplicit('AP'.$cellIndex,$value['minsheng_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AQ'.$cellIndex,$value['minsheng_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AR'.$cellIndex,$value['minsheng'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AS'.$cellIndex,$value['pingan_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AT'.$cellIndex,$value['pingan_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AU'.$cellIndex,$value['pingan'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AV'.$cellIndex,$value['zhongxin_price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AW'.$cellIndex,$value['zhongxin_S_AMT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('AX'.$cellIndex,$value['zhongxin'],\PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('AY'.$cellIndex,$value['qty'],\PHPExcel_Cell_DataType::TYPE_STRING);
            // Attachment end

            $currentSheet->setCellValueExplicit('AZ'.$cellIndex,$value['remark'],\PHPExcel_Cell_DataType::TYPE_STRING);

        }

        $name = uniqid().'.xls';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name );
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    function doXiaJia()
    {
        $respone = array('code' => 0, 'msg' => '');
        $postData = json_decode(I('data'), 1);
        $list = $postData['data'];
        $msg = '操作成功<br/>';

        // 验证权限
        $emp_no = $_SESSION['emp_no'];
        $sapbankWatchModel = M("sapbankWatch");
        $watchEnable = $sapbankWatchModel->where("emp_no='{$emp_no}'")->find();
        if($watchEnable){
            $customerCodeList = explode(',',$watchEnable['Customer_Code']);
            if (in_array($postData['Customer_Code'],$customerCodeList)){
                // go on
            }else{
                return;
            }
        }
        else{
            return;
        }

        foreach ($list as $key => $val) {
            $val['Customer_Code'] = $postData['Customer_Code'];
            unset($val['check']);
            $res = $this->doItemXiaJia($val);
            if ($res['code']) {
                $msg .= $res['msg'] . '<br/>';
            }
        }
        $respone['msg'] = $msg;
        echo json_encode($respone, JSON_UNESCAPED_UNICODE);
        exit;
    }

    function doItemXiaJia($rowData)
    {
        $respone = array('code' => 0, 'msg' => '');
        unset($rowData['_id'], $rowData['_uid'], $rowData['_status']);

        // 一切正常后下架物料
        $sapbankRelationModel = M('sapbankRelation');
        $saveData = array();
        $saveData['Active'] = 0;
        $sapbankRelationModel->where("`id` = '{$rowData['relation_id']}' and Customer_Code = '{$rowData['Customer_Code']}'")->save($saveData);

        // LOG下架动作
        $xiajiaLogModel = M('xiajiaLog');
        $emp_no = $_SESSION['emp_no'];
        $logData = array(
            'relation_id' => $rowData['relation_id'],
            'oa_id' => $rowData['oa_id'],
            'SAP_ItemCode' => $rowData['SAP_ItemCode'],
            'SAP_ItemName' => $rowData['SAP_ItemName'],
            'Customer_Code' => $rowData['Customer_Code'],
            'BANK_ItemCode' => $rowData['BANK_ItemCode'],
            'shelves_list_pm' => $rowData['shelves_list_pm'],
            'xiajia_op' => $emp_no,
            'create_time' => date('Y-m-d H:i:s'),
        );
        $xiajiaLogModel->add($logData);

        //2017-07-04根据2017-06-30会议内容，考虑产品池的需求扩展 think_shelves_detailed 中的 status，更新为：'状态: 0默认 1通知发出 2提报 3上架 4下架'
        $shelvesDetailedData = array('status' => 4,'offline_time'=>date('Y-m-d H:i:s'));
        $shelvesDetailedModel = M('ShelvesDetailed');
        $result = $shelvesDetailedModel->where("id={$rowData['shelves_detailed_id']}")->save($shelvesDetailedData);

        return $respone;
    }

    public function poplistofshelves()
    {
        $customerCode = I('customerCode');
        $this->assign("customerCode",$customerCode);
        $this->display();
    }

    public function listshelves()
    {
        //$customerCode = I('customerCode');
        $customerCode = $_REQUEST['customerCode'];
        $key = I('key');
        $shelvesListModel = M('ShelvesList');
        $where = "1 = 1 ";
        $emp_no = $_SESSION['emp_no'];
        $where .= " and to_emp like '%{$emp_no}%'";
        $where .= " and u_code = '{$customerCode}'";

        $pageIndex = I('pageIndex');//?intval(I('pageIndex'))-1:0;
        $pageSize = I('pageSize')?:50;

        if($key)
        {
            $where .= "and name like '%{$key}%'";
            $result = $shelvesListModel->where($where)->order("id desc")->limit($pageIndex*$pageSize,$pageSize)->select();
        }
        else{
            $result = $shelvesListModel->where($where)->order("id desc")->limit($pageIndex*$pageSize,$pageSize)->select();
        }

        $total =$shelvesListModel->where($where)->count();
        $data = array('total'=>$total,'data'=>$result);
        echo    json_encode($data,JSON_UNESCAPED_UNICODE);

    }


}